﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using Excel;
using System.Windows.Forms;

using Bll;
using System.IO;
using System.Data.OleDb;

namespace StockServer
{
    public partial class SpredInfo : Form
    {
        DetailsCustomer dcr = new DetailsCustomer();
        public SpredInfo()
        {
            InitializeComponent();
           
        }

        private void SpredInfo_Load(object sender, EventArgs e)
        {
            this.dataGridView1.DataSource = dcr.getDataSetSprit().Tables[0].DefaultView;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.dataGridView1.DataSource = dcr.getDataSetSprit().Tables[0].DefaultView;
        }


        public void writerExl()
        {
            //FileInfo fo = new FileInfo(System.Windows.Forms.Application.StartupPath + @"\test.xls");
            //fo.Delete();
            //FileStream fs = new FileStream(System.Windows.Forms.Application.StartupPath + @"\test.xls", FileMode.OpenOrCreate, FileAccess.Write);
            //StreamWriter sw = new StreamWriter(fs);
            //string str = "";
            //try
            //{
            //    //写标题
            //    for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
            //    {
            //        if (i > 0)
            //        {
            //            str += "\t";
            //        }

            //        str += this.dataGridView1.Columns[i].HeaderText;
            //    }

            //    sw.WriteLine(str);


            //    //写内容
            //    for (int j = 0; j < this.dataGridView1.Rows.Count; j++)
            //    {
            //        string tempStr = "";
            //        for (int k = 0; k < this.dataGridView1.Columns.Count; k++)
            //        {
            //            if (k > 0)
            //            {
            //                tempStr += "\t";
            //            }
            //            tempStr += this.dataGridView1.Rows[j].Cells[k].Value + "";
            //        }
            //        sw.WriteLine(tempStr);
            //    }

            //    sw.Close();
            //    fs.Close();
            //    MessageBox.Show("导出成功！");
            //}
            //catch (Exception ex)
            //{

            //    ex.GetBaseException();
            //}
        
        
        }

        public void wrEXLOk()
        {


            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
            saveFileDialog.FilterIndex = 0;
            saveFileDialog.RestoreDirectory = true;
            saveFileDialog.CreatePrompt = true;
            saveFileDialog.Title = "Export Excel File To";
            saveFileDialog.ShowDialog();
            Stream myStream;
            myStream = saveFileDialog.OpenFile();
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            string str = "";
            try
            {
                //写标题
                for (int i = 0; i < this.dataGridView1.ColumnCount; i++)
                {
                    if (i > 0)
                    {
                        str += "\t";
                    }

                    str += this.dataGridView1.Columns[i].HeaderText;
                }
                sw.WriteLine(str);
                //写内容
                for (int j = 0; j < this.dataGridView1.Rows.Count; j++)
                {
                    string tempStr = "";
                    for (int k = 0; k < this.dataGridView1.Columns.Count; k++)
                    {
                        if (k > 0)
                        {
                            tempStr += "\t";
                        }
                        tempStr += this.dataGridView1.Rows[j].Cells[k].Value + "";
                    }
                    sw.WriteLine(tempStr);
                }

                sw.Close();
                myStream.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            finally
            {
                sw.Close();
                myStream.Close();
            }
            MessageBox.Show("OK");
        }
        private void button1_Click(object sender, EventArgs e)
        {
            wrEXLOk();
           
        }

        public void ISokW()
        {
            int Id = 0;
            string Name = "测试";
            string FileName = System.Windows.Forms.Application.StartupPath + @"\test.xls";
            System.Data.DataTable dt = new System.Data.DataTable();
            long totalCount = dt.Rows.Count;
            long rowRead = 0;
            float percent = 0;
            OleDbParameter[] parm = new OleDbParameter[dt.Columns.Count];
            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data   Source=" + FileName + ";Extended   Properties=Excel   8.0;";
            OleDbConnection objConn = new OleDbConnection(connString);
            OleDbCommand objCmd = new OleDbCommand();
            objCmd.Connection = objConn;
            objConn.Open();
            //建立表结构   
            objCmd.CommandText = @"CREATE   TABLE   Sheet1(序号   Integer,名称   varchar)";
            objCmd.ExecuteNonQuery();
            //建立插入动作的Command   
            objCmd.CommandText = "INSERT   INTO   Sheet1(" + Id + "," + Name + ")";
            parm[0] = new OleDbParameter("@Id", OleDbType.Integer);
            objCmd.Parameters.Add(parm[0]);
            parm[1] = new OleDbParameter("@Company", OleDbType.VarChar);
            objCmd.Parameters.Add(parm[1]);
            //遍历DataTable将数据插入新建的Excel文件中   
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                parm[0].Value = i + 1;
                for (int j = 1; j < parm.Length; j++)
                {
                    parm[j].Value = dt.Rows[i][j];
                }
                objCmd.ExecuteNonQuery();
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
                // this.FM.CaptionText.Text   =   "正在导出数据，已导出["   +   percent.ToString("0.00")   +   "%]...";   
                if (i == dt.Rows.Count - 1)
                    // this.FM.CaptionText.Text   =   "请稍后......";   
                    System.Windows.Forms.Application.DoEvents();
            }
            objConn.Close();
            //this.FM.CaptionText.Text   =   "";   

        
        
        }
        public void print(DataGridView dataGridView1)
        {
            //导出到execl   
            try 
            {
                //没有数据的话就不往下执行   
                if (dataGridView1.Rows.Count == 0)
                    return;
                //实例化一个Excel.Application对象   
              Excel.Application excel = new Excel.Application();
                //让后台执行设置为不可见，为true的话会看到打开一个Excel，然后数据在往里写   
                excel.Visible = false;
                //新增加一个工作簿，Workbook是直接保存，不会弹出保存对话框，加上Application会弹出保存对话框，值为false会报错
                
                excel.Application.Workbooks.Add(true);
                //生成Excel中列头名称   
                for (int i = 0; i < dataGridView1.Columns.Count; i++)
                {
                    excel.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
                }

                //把DataGridView当前页的数据保存在Excel中   
                for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridView1.Columns.Count; j++)
                    {
                        if (dataGridView1[j, i].ValueType == typeof(string))
                        {
                            excel.Cells[i + 2, j + 1] = "'" + dataGridView1[j, i].Value.ToString();
                        }
                        else
                        {
                            excel.Cells[i + 2, j + 1] = dataGridView1[j, i].Value.ToString();
                        }
                    }
                }

                //设置禁止弹出保存和覆盖的询问提示框   
                excel.DisplayAlerts = false;
                excel.AlertBeforeOverwriting = false;

                //保存工作簿   
                excel.Application.Workbooks.Add(true).Save();
                //保存excel文件   
                excel.Save(System.Windows.Forms.Application.StartupPath + @"\test.xls");


                //确保Excel进程关闭   
                excel.Quit();
                excel = null;
                MessageBox.Show("保存成功");

            
            }
            catch (Exception ex)
            { 
                ex.GetBaseException();
            }
        }

    }
}